package dao;
import java.sql.*;
import java.util.Vector;

import beans.Filme;
import beans.Genero;

public class MysqlDAO {

	Connection conn;
	Statement stmt;
	PreparedStatement pstmt;
	ResultSet rs;
	
	
	public MysqlDAO() {
		getConexao();
		
	}
	
	
	
	public Connection getConexao(){
		
		try {
			Driver.class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/filmes","root","123456");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	
	public void closeConexao(){
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Vector<Genero> listaGen(){
		Vector<Genero> Generos = new Vector<Genero>();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from generos order by nome");
			while(rs.next()){
				Genero Gen = new Genero();
				Gen.setCodigo(rs.getInt("codigo_genero"));
				Gen.setNome(rs.getString("nome"));
				Generos.add(Gen);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return Generos;
	}
	
	public Vector<Filme> listFilmes(){
		Vector<Filme> Filmes = new Vector<Filme>();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select f.codigo_filmes as CodFilmes,f.nome as Filme,g.nome as Genero,f.data as Data			from filmes f,filmes_generos e, generos g where f.codigo_filmes = e.filme and g.codigo_genero = e.genero order by f.nome");
			while(rs.next()){
				Filme flm = new Filme();
				flm.setCodigo(rs.getInt("codfilmes"));
				flm.setNome(rs.getString("Filme"));
				flm.setGenero(rs.getString("genero"));
				flm.setData(rs.getString("data"));
				Filmes.add(flm);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return Filmes;
	}
	
	public void cadastarFilmes(Filme flm){
		try {
			pstmt = conn.prepareStatement("insert into filmes(nome,data) values(?,?)");
			pstmt.setString(1, flm.getNome());
			pstmt.setString(2, flm.getData());
			pstmt.execute();
			pstmt = conn.prepareStatement("select codigo_filmes as codigo from filmes where nome = ?");
			pstmt.setString(1, flm.getNome());
			rs = pstmt.executeQuery();
			rs.next();
			int cod = rs.getInt("codigo");
			pstmt = conn.prepareStatement("insert into filmes_generos(filme,genero) values(?,?)");
			pstmt.setInt(1, cod);
			pstmt.setInt(2, flm.getCodigo_genero());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}
